import requests
from lxml import etree
# 导入PyMySQL模块用于跟MySQL交互
import pymysql.cursors

book_base_url = 'https://book.douban.com/top250?start={}'
headers = {
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36"
}

connection = pymysql.connect(host='192.168.44.128',
                             user='root',
                             password='123456',
                             database='spiders',
                             # MySQL驱动
                             cursorclass=pymysql.cursors.DictCursor)
# 将数据写入MySQL中
# 使用with建立连接可以在用完时自动关闭
with connection:
    # 建立连接并创建游标cursor
    # 游标cursor：用于执行SQL以及获取结果
    with connection.cursor() as cursor:
        """ MySQL的建表语句
        DROP TABLE IF EXISTS `book_info`;
        CREATE TABLE `book_info` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `name` varchar(255) DEFAULT NULL COMMENT '书籍名称',
          `price` varchar(255) DEFAULT NULL COMMENT '书籍价格',
          `date` varchar(255) DEFAULT NULL COMMENT '出版日期',
          `publish` varchar(255) DEFAULT NULL COMMENT '出版商',
          `authors` varchar(255) DEFAULT NULL COMMENT '书籍作者',
          `rating_nums` double DEFAULT NULL COMMENT '书籍评分',
          `quote` varchar(500) DEFAULT NULL COMMENT '书籍引言',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
        """

        insert_sql = 'insert into book_info (name,price,date,publish,authors,rating_nums,quote) values (%s,%s,%s,%s,%s,%s,%s)'

        for i in range(1, 11):
            book_url = book_base_url.format((i - 1) * 25)
            # 发起请求
            book_response = requests.get(url=book_url, headers=headers)
            print("当前正在处理:%s" % book_url)
            book_xpath = etree.HTML(book_response.text, etree.HTMLParser())
            tr_list = book_xpath.xpath("//tr[@class='item']")
            for tr in tr_list:
                # 提取每本书籍的信息
                name = tr.xpath(".//div[@class='pl2']/a/text()")[0].strip()
                book_infos = tr.xpath(".//p[@class='pl']/text()")[0].strip()
                book_infos_list = book_infos.split(" / ")
                # 通过逆向索引从右往左取值
                price = book_infos_list[-1].strip()
                date = book_infos_list[-2].strip()
                publish = book_infos_list[-3].strip()
                # 通过切片提取所有的作者
                authors_list = book_infos_list[-4::-1]
                # 通过字符串的join方法可以将list转成字符串
                authors = " | ".join(authors_list)

                rating_nums = tr.xpath(".//span[@class='rating_nums']/text()")[0]
                quote_span = tr.xpath(".//p[@class='quote']/span/text()")
                quote = ''
                if quote_span:
                    quote = quote_span[0]
                # 将提取到的书籍信息数据写入MySQL
                cursor.execute(insert_sql, (name, price, date, publish, authors, rating_nums, quote))
            # 提交事务保证写入的数据能够在数据库中生效
            connection.commit()
